﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Collections;
using System.Data.SqlClient;
using cash_desk_model.src.model;

namespace cash_desk_model
{
    public class CashDeskService
    {
        private const String _connString = @"Data Source=COCO-PC\SQLEXPRESS; Initial Catalog=CASH-DESK-DB; Integrated Security=true";
        private static SqlConnection _dbConn = null;

        public static void SetupDBConn()
        {
            // Create database connection
            _dbConn = new SqlConnection();
            _dbConn.ConnectionString = _connString;
            _dbConn.Open();
        }

        public static void DestroyDBConn()
        {
            // Close databacse connection
            _dbConn.Close();
        }

        public Product GetProduct(String code)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandText = "GetProduct";
            command.Connection = _dbConn;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar).Value = code;
            SqlDataReader reader = command.ExecuteReader();

            // Get Product by code
            Product product = new Product();
            if (reader.Read())
            {
                product.ID = Int32.Parse(reader["ID"].ToString());
                product.ProductName = reader["NAME"].ToString();
                product.Code = reader["CODE"].ToString();
                product.Type = reader["TYPE"].ToString();
                product.Price = Double.Parse(reader["PRICE"].ToString()) / 100;
                product.Quantity = Double.Parse(reader["QUANTITY"].ToString());
                product.Description = reader["DESCRIPTION"].ToString();
                product.Provider = reader["PROVIDER"].ToString();
                reader.Close();
                return product;
            }
            reader.Close();

            return null;
        }

        public int SavePayment(DateTime payDate, String payType, Double amount, String cardId, String cashierName)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "SavePayment";
            command.Connection = _dbConn;

            // Save new payment
            command.Parameters.Add("@payDate", System.Data.SqlDbType.DateTime).Value = payDate;
            command.Parameters.Add("@payType", System.Data.SqlDbType.NVarChar, 50).Value = payType;
            command.Parameters.Add("@amount", System.Data.SqlDbType.Money).Value = amount;
            command.Parameters.Add("@cardId", System.Data.SqlDbType.NVarChar, 50).Value = cardId;
            command.Parameters.Add("@cashierName", System.Data.SqlDbType.NVarChar, 100).Value = cashierName;

            return Convert.ToInt32(command.ExecuteScalar());
        }

        public Double GetProductQuantity(String code)
        {
            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Connection = _dbConn;

            // Get product quantity
            command.CommandText = "GetProductQuantity";
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = code;
            return Double.Parse(command.ExecuteScalar().ToString());
        }

        public int UpdateProductQuantity(String code, Double quantity)
        {
            // Get old product quantity
            Double oldQulantity = this.GetProductQuantity(code);

            // Get new quantity
            Double newQuantity = oldQulantity - quantity;

            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "UpdateProductQuantity";
            command.Connection = _dbConn;

            // Save new password
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = code;
            command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = newQuantity;

            return command.ExecuteNonQuery();
        }

        public int SaveSoldProduct(long paymentId, String productCode, Double price, Double quantity)
        {
            // Get product by code
            Product soldProduct = this.GetProduct(productCode);

            // Call sql procedure
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "SaveSoldProduct";
            command.Connection = _dbConn;

            // Save sold product
            command.Parameters.Add("@paymentId", System.Data.SqlDbType.Int).Value = paymentId;
            command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 255).Value = soldProduct.ProductName;
            command.Parameters.Add("@code", System.Data.SqlDbType.NVarChar, 100).Value = productCode;
            command.Parameters.Add("@type", System.Data.SqlDbType.NVarChar, 100).Value = soldProduct.Type;
            command.Parameters.Add("@price", System.Data.SqlDbType.Money).Value = price;
            command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal).Value = quantity;

            return command.ExecuteNonQuery();
        }
    }


}
